package apibasej.db.server.exec;

import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;

import javax.sql.DataSource;

import apibasej.basic.exception.APIBaseException;
import apibasej.basic.misc.UtilStrObj;
import apibasej.basic.misc.standalone.TwoValues;
import apibasej.basic.misc.standalone.UtilMath;
import apibasej.db.metadata.Col;
import apibasej.db.sql.wrapper.ColSelect;
import apibasej.db.sql.wrapper.SQLWrapperException;
import apibasej.db.transaction.UtilDBException;
import apibasej.db.transaction.UtilDBStr;

public class SequenceDBManager<T extends Number> {

	private static int sizeIncrement = 1;
	
	private String tab;
	private String colSeq;
	private Class<T> classColPK;
	//private TransactionServer uc;
	private DataSource ds; //deve usar um DS, pois se der erro a transação com a mesma conexão ficará dando o erro: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
	private TwoValues<String,Object>[] othersFilters;
	
	//TODO ver opção de passar strings para tratar sequences ... 
	
	@SafeVarargs
	public SequenceDBManager(String tab, String colSeq, Class<T> classColPK, DataSource ds, TwoValues<String, Object>... othersFilters) {
		this.tab = tab;
		this.colSeq = colSeq;
		this.classColPK = classColPK;
		this.ds = ds;
		this.othersFilters = othersFilters;
	}
	
	@SuppressWarnings("unchecked")
	public <V> SequenceDBManager(Col<T> colSeq, DataSource ds, TwoValues<Col<V>,V>... othersFilters2) throws SQLWrapperException, UtilDBException{
		this.tab = colSeq.getTab().getName();
		this.colSeq = colSeq.getName();
		this.classColPK = colSeq.getClassCol();
		this.ds = ds;
		
		this.othersFilters = null;
		if(othersFilters2!=null && othersFilters2.length>0){
			this.othersFilters = new TwoValues[othersFilters2.length];
			int ct = 0;
			for(TwoValues<Col<V>,V> f : othersFilters2){
				this.othersFilters[ct++] = new TwoValues<String, Object>(f.getFirst().getName(), f.getSecond());
			}
		}
	}
	
	
	@SafeVarargs
	public static <T extends Number,V> T getNextSeq(Col<T> colSeq, DataSource ds, TwoValues<Col<V>,V>... othersFilters2) throws SQLWrapperException, UtilDBException, SQLException, IllegalArgumentException, SecurityException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, ClassNotFoundException, ParseException, APIBaseException, IOException{
		SequenceDBManager<T> s = new SequenceDBManager<>(colSeq, ds, othersFilters2);
		return s.getNextSeq();
	}
	
	
	private T getNextValSequence(boolean tryCreate) throws SQLException, UtilDBException, SQLWrapperException, IllegalArgumentException, SecurityException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, ClassNotFoundException, ParseException, APIBaseException, IOException{
		try(Connection c = ds.getConnection()){
			c.setAutoCommit(true);//para confirmar a criação da sequence automaticamente
			String nameSequence = getNameSequence();
			try{
				//System.out.println("111>"+nameSequence);
				try(PreparedStatement ps = c.prepareStatement("SELECT NEXTVAL('"+nameSequence+"')")){
					//ps.setString(1, nameSequence);
					try(ResultSet rs = ps.executeQuery()){
						rs.next();
						T resp = UtilStrObj.toObj(rs.getString(1), classColPK);//(T)convertForColType(rs.getObject(1),false);
						//System.out.println("RRR>"+resp+" - "+resp.getClass());
						return resp;
					}
				}
			}catch(SQLException e){
				//tenta criar a sequence se der erro ... para criar se sequence não existe ...
				if(tryCreate){
					//System.out.println("222>"+nameSequence+" - "+getMaxVal());
					try(PreparedStatement ps = c.prepareStatement("CREATE SEQUENCE "+nameSequence+" START "+getMaxVal()+" INCREMENT "+getSizeIncrement())){
						//ps.setString(1, nameSequence);
						//ps.setObject(2, getMaxVal());
						ps.execute();
					}
					//depois de criar retorna pela sequence ...
					return getNextValSequence(false);
				}else{
					throw e;
				}
			}
		}
	}
	
	private String getNameSequence(){
		StringBuilder sbKey = new StringBuilder("seq_");//tab+"."+colSeq;
		String t = tab.replaceAll("_","");
		sbKey.append(t);
		//sbKey.append('.');
		String colSeqStr = colSeq.replaceAll("_","");
		sbKey.append(colSeqStr);
		if(othersFilters!=null && othersFilters.length>0){
			sbKey.append("_");
			for(TwoValues<String,Object> f : othersFilters){
				//sbKey.append(';');
				sbKey.append( f.getFirst().replaceAll("_","") );
				//sbKey.append('-');
				sbKey.append(f.getSecond());
			}
		}
		return sbKey.toString();
	}
	
	public T getMaxVal() throws SQLWrapperException, UtilDBException{
		UtilDBStr utilDB = new UtilDBStr(tab);
		ColSelect<?> c = utilDB.addColSelect(colSeq);
		c.setStrForColSelect("MAX("+colSeq+")");
		c.setReturnPrimitiveIfNull(true); //precisa?
		if(othersFilters!=null){
			for(TwoValues<String,Object> f : othersFilters){
				utilDB.addFilter(f.getFirst(), f.getSecond());
			}
		}
		Object m = utilDB.execSelectUniqueValue(new TransactionServer(ds));
		return convertForColType(m);
	}

	private T convertForColType(Object val) throws SQLWrapperException, UtilDBException{
		
		//if(lastVal==null) lastVal = getSizeIncrement();
		//if(lastVal==null) throw new UtilDBException("lastVal==null ! ");// não deve ocorrer pois o MAX está com setReturnPrimitiveIfNull(true)
		
		int inctForResp = getSizeIncrement();//inc?getSizeIncrement():0;
		
		return UtilMath.sum(classColPK, val, inctForResp);
		/*
		if( Integer.class.isAssignableFrom(classColPK) ) {
			if(val==null) val = (int)0;
			return ((Integer)val)+inctForResp;
		}else if( Long.class.isAssignableFrom(classColPK) ) {
			if(val==null) val = (long)0;
			return ((Long)val)+inctForResp;
		}else if( Short.class.isAssignableFrom(classColPK) ) {
			if(val==null) val = (short)0;
			return ((Short)val)+inctForResp;
		}else if( Byte.class.isAssignableFrom(classColPK) ) {
			if(val==null) val = (byte)0;
			return ((Byte)val)+inctForResp;
		}else if( String.class.isAssignableFrom(classColPK) ) {
			if(val==null) val = "0";
			return ""+(Integer.parseInt(""+val)+inctForResp);			
		}
		
		else throw new UtilDBException("Invalid type for PK column: "+tab+"."+colSeq+", typeCol: "+classColPK.getName()+", typeLastVal: "+val.getClass().getName());
		*/
	}
	
	
	
	public T getNextSeq() throws SQLWrapperException, UtilDBException, SQLException, IllegalArgumentException, SecurityException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException, ClassNotFoundException, ParseException, APIBaseException, IOException{
		return getNextValSequence(true);
	}
	
	


	public static int getSizeIncrement() {
		return sizeIncrement;
	}
	public static void setSizeIncrement(int size) {
		sizeIncrement = size;
	}
	
	
}
